package org.fastsyncer.parser.database;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.fastsyncer.common.entity.DataSourceConfig;
import org.fastsyncer.common.entity.MappingTask;
import org.fastsyncer.connector.common.DatabaseConstant;
import org.fastsyncer.connector.database.DatabaseConnector;
import org.fastsyncer.connector.framework.ConnectorFactory;
import org.fastsyncer.connector.util.DatabaseUtil;
import org.json.JSONArray;
import org.json.JSONObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;

public class DatabaseParser {

	// 实现单例模式的方法，通过方法来实现实例化
	// 声明一个私有的静态属性，使只被实例化一次
	private static final DatabaseParser instance = new DatabaseParser();

	// 声明一个私有的构造方法，使别人不能随便new对象
	// 它只被调用一次，实例存在之后就不再调用了
	private DatabaseParser() {
	}

	// 声明一个公共的静态方法，外界通过调用这个方法来得到唯一的实例
	public static DatabaseParser getInstance() {
		return instance;
	}

	private static Logger logger = LoggerFactory.getLogger(DatabaseParser.class);

	/**
	 * 解析数据源Database类型
	 * 
	 * @param mappingTask
	 * @param startPos
	 * @param endPos
	 * @return
	 * @throws Exception
	 */
	public List<Map<String, Object>> parseSourceDatabase(MappingTask mappingTask, Integer startPos, Integer endPos)
			throws Exception {
		DatabaseConnector conn = (DatabaseConnector) ConnectorFactory.getInstance(mappingTask.getSourceConnector());
		// 1.获取源所有数据,例如 [{ID=123, NAME=张三11}, {ID=321, NAME=李四},...]
		// 数据源实例
		DataSourceConfig sourceConfig = (DataSourceConfig) mappingTask.getSourceConfig();
		JdbcTemplate sourceJdbc = conn.getJdbcTemplate(sourceConfig);
		String sql = mappingTask.getSourceExcuteCommond().get(DatabaseConstant.OPERTION_SQL_QUERY);
		Object[] args = null;
		String driver = sourceConfig.getDriverClassName();
		if (StringUtils.equalsIgnoreCase(DatabaseConstant.DB_MYSQL_DRIVER_CLASSNAME, driver)) {
			args = new Object[] { startPos, endPos };
		} else if (StringUtils.equalsIgnoreCase(DatabaseConstant.DB_ORACLE_DRIVER_CLASSNAME, driver)) {
			args = new Object[] { endPos, startPos };
		}

		List<Map<String, Object>> rows = sourceJdbc.queryForList(sql, args);
		sourceJdbc.getDataSource().getConnection().close();
		return rows;
	}
	
	/**
	 * 同步数据源Database类型
	 * 
	 * @param mappingTask
	 * @param rows
	 * @return
	 * @throws Exception
	 */
	public Integer[] syncTargetDatabase(MappingTask mappingTask, List<Map<String, Object>> rows) throws Exception {
		DatabaseConnector conn = (DatabaseConnector) ConnectorFactory.getInstance(mappingTask.getTargetConnector());
		// 批量条数
		int size = rows.size();
		BatchPreparedStatementSetter batchSetter = new BatchPreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				// 2.获取源同步字段sourcePro:[{name=ID, type=4}, {name=NAME, type=12}]
				// 获取每行中的所有字段值，例如 {ID=123, NAME=张三11}
				conn.batchRowsSetter(ps, mappingTask.getSourceFileds(), rows.get(i));
			}

			@Override
			public int getBatchSize() {
				return size;
			}
		};

		// 目标源实例
		DataSourceConfig targetConfig = (DataSourceConfig) mappingTask.getTargetConfig();
		JdbcTemplate targetJdbc = conn.getJdbcTemplate(targetConfig);
		// 执行批量同步
		String sql = mappingTask.getTargetExcuteCommond().get(DatabaseConstant.OPERTION_SQL_INSERT);
		int[] batchUpdate = targetJdbc.batchUpdate(sql, batchSetter);
		int total = batchUpdate.length;
		int success = 0;
		for (int i = 0; i < total; i++) {
			// 目前在批量同步Oracle时，成功会返回-2
			success += (batchUpdate[i] == 1 || batchUpdate[i] == -2) ? 1 : 0;
		}
		targetJdbc.getDataSource().getConnection().close();
		return new Integer[] { total, success };
	}

	/**
	 * 增量同步修改到目标源
	 * 
	 * @param mappingTask
	 * @param row
	 * @return
	 * @throws Exception
	 */
	public boolean updateToTargetDatabase(MappingTask mappingTask, JSONArray after)
			throws Exception {
		logger.info(after.toString());
		Map<String, Object> row = new HashMap<String, Object>();

		// 1.解析数据源
		int len = after.length();
		List<HashMap<String, Object>> sourceFileds = mappingTask.getSourceFileds();
		for (HashMap<String, Object> fileds : sourceFileds) {
			String name = (String) fileds.get("name");
			for (int i = 0; i < len; i++) {
				JSONObject f = (JSONObject) after.get(i);
				String fName = f.getString("name");

				if (StringUtils.equals(name, fName)) {
					Object v = f.getString("value");
					row.put(fName, v);
					break;
				}
			}
		}

		// 2.解析数据源主键字段
		DataSourceConfig sourceConfig = (DataSourceConfig) mappingTask.getSourceConfig();
		String sourcePK = sourceConfig.getPrimaryKey().toUpperCase();
		if (StringUtils.isBlank(sourcePK)) {
			logger.error("主键不能为空!");
			throw new NullPointerException("主键不能为空!");
		}

		// 判断是否为主键对应值
		Object sourcePKValue = row.get(sourcePK);
		if (sourcePKValue == null) {
			logger.error("主键值不能为空!");
			throw new NullPointerException("主键值不能为空!");
		}

		// 主键值
		final Object sPKValue = sourcePKValue;
		PreparedStatementSetter setter = new PreparedStatementSetter() {
			public void setValues(PreparedStatement ps) throws SQLException {
				// 获取源同步字段sourcePro:[{name=ID, type=4}, {name=NAME, type=12}]
				// 获取每行中的所有字段值，例如 {ID=123, NAME=张三11}

				// 设置值参数
				int sourcePKType = 12;
				int filedSize = sourceFileds.size();
				for (int i = 0; i < filedSize; i++) {
					// 取出字段和对应值
					HashMap<String, Object> filed = sourceFileds.get(i);
					String name = (String) filed.get("name");
					int type = Integer.parseInt(filed.get("type").toString());
					// 匹配主键类型
					sourcePKType = sourcePK.equals(name) ? type : sourcePKType;
					Object value = row.get(name);
					DatabaseUtil.preparedStatementSetter(ps, i + 1, type, value);
				}

				// 设置主键参数
				DatabaseUtil.preparedStatementSetter(ps, filedSize + 1, sourcePKType, sPKValue);

			}
		};

		// 执行更新
		DatabaseConnector conn = (DatabaseConnector) ConnectorFactory.getInstance(mappingTask.getTargetConnector());
		DataSourceConfig targetConfig = (DataSourceConfig) mappingTask.getTargetConfig();
		JdbcTemplate jdbcTemplate = conn.getJdbcTemplate(targetConfig);

		// 获取更新语句
		String sql = mappingTask.getTargetExcuteCommond().get(DatabaseConstant.OPERTION_SQL_UPDATE);
		logger.info(sql);

		int update = jdbcTemplate.update(sql, setter);
		logger.info("update:" + update);
		if (update < 1) {
			throw new Exception("执行更新失败,请检查参数，或联系系统管理员!");
		}
		return true;
	}

	/**
	 * 增量同步新增到目标源
	 * 
	 * @param mappingTask
	 * @param row
	 * @return
	 * @throws Exception
	 */
	public boolean insertToTargetDatabase(MappingTask mappingTask, JSONArray after) throws Exception {
		logger.info(after.toString());
		Map<String, Object> row = new HashMap<String, Object>();
		// 1.解析数据源
		int len = after.length();
		List<HashMap<String, Object>> sourceFileds = mappingTask.getSourceFileds();
		for (HashMap<String, Object> fileds : sourceFileds) {
			String name = (String) fileds.get("name");
			for (int i = 0; i < len; i++) {
				JSONObject f = (JSONObject) after.get(i);
				String fName = f.getString("name");

				if (StringUtils.equals(name, fName)) {
					String v = f.getString("value");
					row.put(fName, v);
					break;
				}
			}
		}

		// 获取新增语句
		String sql = mappingTask.getTargetExcuteCommond().get(DatabaseConstant.OPERTION_SQL_INSERT);
		logger.info(sql);

		// 执行新增
		DatabaseConnector conn = (DatabaseConnector) ConnectorFactory.getInstance(mappingTask.getTargetConnector());
		DataSourceConfig targetConfig = (DataSourceConfig) mappingTask.getTargetConfig();
		JdbcTemplate jdbcTemplate = conn.getJdbcTemplate(targetConfig);

		PreparedStatementSetter setter = new PreparedStatementSetter() {
			public void setValues(PreparedStatement ps) throws SQLException {
				// 设置值参数
				int filedSize = sourceFileds.size();
				for (int i = 0; i < filedSize; i++) {
					// 取出字段和对应值
					HashMap<String, Object> filed = sourceFileds.get(i);
					String name = (String) filed.get("name");
					int type = Integer.parseInt(filed.get("type").toString());
					Object value = row.get(name);
					DatabaseUtil.preparedStatementSetter(ps, i + 1, type, value);
				}
			}
		};

		int insert = jdbcTemplate.update(sql, setter);
		logger.info("insert:" + insert);
		if (insert < 1) {
			throw new Exception("执行新增失败,请检查参数，或联系系统管理员!");
		}
		return true;
	}

	/**
	 * 增量同步删除到目标源
	 * 
	 * @param mappingTask
	 * @param row
	 * @return
	 * @throws Exception
	 */
	public boolean deleteToTargetDatabase(MappingTask mappingTask, JSONArray before) throws Exception {
		logger.info(before.toString());
		List<Object> args = new ArrayList<Object>();

		// 2.解析数据源主键字段
		DataSourceConfig sourceConfig = (DataSourceConfig) mappingTask.getSourceConfig();
		String sourcePK = sourceConfig.getPrimaryKey();
		if (StringUtils.isBlank(sourcePK)) {
			logger.error("主键不能为空!");
			throw new NullPointerException("主键不能为空!");
		}

		// 判断是否为主键对应值
		String sourcePKValue = null;
		int bLen = before.length();
		for (int i = 0; i < bLen; i++) {
			JSONObject f = (JSONObject) before.get(i);
			String fName = f.getString("name");
			if (sourcePK.equalsIgnoreCase(fName)) {
				sourcePKValue = f.getString("value");
				break;
			}
		}

		if (StringUtils.isBlank(sourcePKValue)) {
			logger.error("主键值不能为空!");
			throw new NullPointerException("主键值不能为空!");
		}
		args.add(sourcePKValue);
		logger.info(args.toString());

		// 获取删除语句
		String sql = mappingTask.getTargetExcuteCommond().get(DatabaseConstant.OPERTION_SQL_DELETE);
		logger.info(sql);

		// 执行删除
		DatabaseConnector conn = (DatabaseConnector) ConnectorFactory.getInstance(mappingTask.getTargetConnector());
		DataSourceConfig targetConfig = (DataSourceConfig) mappingTask.getTargetConfig();
		JdbcTemplate jdbcTemplate = conn.getJdbcTemplate(targetConfig);
		int delete = jdbcTemplate.update(sql, args.toArray());
		logger.info("delete:" + delete);
		if (delete < 1) {
			throw new Exception("执行删除失败,请检查参数，或联系系统管理员!");
		}
		return true;
	}

}
